Create PROCEDURE [dbo].[Club_SaveInfo]
    @TmpId NUMERIC(18, 0),
    @lastId NUMERIC(18, 0),
    @FactorId UNIQUEIDENTIFIER
AS
DECLARE @DefinePrice MONEY;
DECLARE @TotPrice MONEY =
        (
            SELECT Tot_Price_forosh FROM dbo.TmpHeadH WHERE Id_Havaleh = @TmpId
        );
DECLARE @ClubId INT =
        (
            SELECT ClubId FROM dbo.TmpHeadH WHERE Id_Havaleh = @TmpId
        );
DECLARE @Year VARCHAR(4) = SUBSTRING(dbo.MiladiToShamsi(GETDATE()), 1, 4);
DECLARE @Score MONEY;
DECLARE @IsDecimal BIT;
DECLARE @StrValue VARCHAR(MAX);
DECLARE @ServiceId TINYINT;
DECLARE @Mobile CHAR(11);
DECLARE @FromDay SMALLINT;
DECLARE @ToDay SMALLINT;
DECLARE @ScorePrice MONEY;
DECLARE @ChargeScore FLOAT;
DECLARE @ChargePrice MONEY;
DECLARE @Percent REAL;
DECLARE @ScoreId INT;
DECLARE @ScoreType TINYINT;
DECLARE @Msg SMALLINT;
DECLARE @StartDate SMALLDATETIME;
DECLARE @EndDate SMALLDATETIME;
DECLARE @FromDate SMALLDATETIME;
DECLARE @ToDate SMALLDATETIME;
DECLARE @PriceScoreGoods MONEY;
DECLARE @ScoreGoods MONEY;
DECLARE @Gold REAL;
DECLARE @Silver REAL;
DECLARE @boronz REAL;
DECLARE @Normal REAL;

DECLARE @zarib REAL;
DECLARE @KindOfservice TINYINT;



DECLARE CursorSelect CURSOR FOR
SELECT ISNULL(Score, 0),
       IsDecimal,
       ISNULL(DefinePrice, 0),
       FromDay,
       ToDay,
       ScorePrice,
       Percent_Club,
       ScoreType,
       StartDate,
       EndDate,
       Service1,
       Service2,
       Service3,
       Service4
FROM dbo.ClubCreditSetting
WHERE CONVERT(VARCHAR(10), GETDATE(), 111) >= CONVERT(VARCHAR(10), StartDate, 111)
      AND CONVERT(VARCHAR(10), GETDATE(), 111) <= CONVERT(VARCHAR(10), EndDate, 111)
      AND IsActive = 1;
OPEN CursorSelect;
FETCH NEXT FROM CursorSelect
INTO @Score,
     @IsDecimal,
     @DefinePrice,
     @FromDay,
     @ToDay,
     @ScorePrice,
     @Percent,
     @ScoreType,
     @StartDate,
     @EndDate,
     @Gold,
     @Silver,
     @boronz,
     @Normal;
WHILE @@FETCH_STATUS = 0
BEGIN

    -----------------------------------شارژ امتيازي--------------------------------- 
    IF @ScoreType = 1
    BEGIN
        SET @ScoreGoods = 0;
        SET @PriceScoreGoods = 0;
        ----------------------شارژ امتياز با نوع سرويس مشتري ------------------------
        -- طايي
        -- نقره اي
        -- برنزي
        -- عادي
        SET @KindOfservice = 0;
        SELECT @Mobile = Mobile,
               @KindOfservice = ServiceType
        FROM dbo.Tbl_ClubH
        WHERE AutoId = @ClubId;
        IF @KindOfservice = 1
            SET @zarib = @Normal;
        ELSE IF @KindOfservice = 2
            SET @zarib = @boronz;
        ELSE IF @KindOfservice = 3
            SET @zarib = @Silver;
        ELSE IF @KindOfservice = 4
            SET @zarib = @Gold;
        ELSE
            SET @zarib = 1;
        -----------------
        SELECT @ScoreGoods
            = ROUND(ISNULL(SUM(CAST(CAST(SumQty AS FLOAT) / CAST(K_Qty AS FLOAT) AS INT) * charge), 0), 0),
               @PriceScoreGoods = SUM(TotForosh)
        FROM
        (
            SELECT K_Code,
                   SUM(K_Qty1) SumQty,
                   SUM(Tot_Forosh) TotForosh
            FROM dbo.TmpHavaleh
            WHERE Id_Havaleh = @TmpId
            GROUP BY K_Code
        ) AS T
            INNER JOIN Tbl_ClubChargePolicy
                ON T.K_Code = dbo.Tbl_ClubChargePolicy.K_code
        WHERE T.SumQty >= K_Qty;

        IF @IsDecimal = 1
        BEGIN
            SET @ChargeScore
                = (CAST(@TotPrice AS DECIMAL(10, 1)) - ISNULL(@PriceScoreGoods, 0))
                  / CAST(@DefinePrice * @zarib AS DECIMAL(10, 1)) * @Score;
            SET @StrValue = CAST(@ChargeScore AS VARCHAR(MAX));
            IF CHARINDEX('.', @ChargeScore) > 0
                SET @ChargeScore = CAST(SUBSTRING(@StrValue, 1, CHARINDEX('.', @StrValue) + 1) AS FLOAT);
        END;
        ELSE
            SET @ChargeScore
                = CAST(((@TotPrice - ISNULL(@PriceScoreGoods, 0)) / (@DefinePrice * @zarib)) AS INT) * @Score;

        IF @ScoreGoods < -10
            SET @ChargeScore = 0;
        ELSE
            SET @ChargeScore = @ChargeScore + @ScoreGoods;
    END;


    ---------------------------شارژ مبلغي با شروع متغير---------------------------


    ELSE IF @ScoreType IN ( 2, 4 )
    BEGIN
        IF @Percent > 0
            SET @Score = (@Percent / 100) * @DefinePrice;
        ELSE IF @ScorePrice > 0
            SET @Score = @ScorePrice;
        IF @IsDecimal = 1
        BEGIN
            SET @ChargePrice = CAST(@TotPrice AS DECIMAL(10, 1)) / CAST(@DefinePrice AS DECIMAL(10, 1)) * @Score;
            SET @StrValue = CAST(@ChargePrice AS VARCHAR(MAX));
            IF CHARINDEX('.', @ChargePrice) > 0
                SET @ChargePrice = CAST(SUBSTRING(@StrValue, 1, CHARINDEX('.', @StrValue) + 1) AS FLOAT);
        END;
        -- ELSE
        IF (@IsDecimal = 0)
           AND (@TotPrice >= @DefinePrice)
        BEGIN

            --	SET @ChargePrice = ROUND( (@Totprice /@DefinePrice) * @Score,0) 1402 12 01
            SET @ChargePrice = FLOOR((@TotPrice / @DefinePrice)) * @Score;

        END;


    END;
    --------------------------------شارژ بر اساس درصد کالا---------------------------
    ELSE IF @ScoreType IN ( 3, 5 )
    BEGIN
        SELECT @ChargePrice
            = ROUND(
                       ISNULL(
                                 SUM(CAST(CAST(SumQty AS FLOAT) / CAST(K_Qty AS FLOAT) AS INT) * [Percent] * TotForosh
                                     / 100
                                    ),
                                 0
                             ),
                       0
                   )
        FROM
        (
            SELECT K_Code,
                   SUM(K_Qty1) SumQty,
                   SUM(Price_Forosh) TotForosh
            FROM dbo.TmpHavaleh
            WHERE Id_Havaleh = @TmpId
            GROUP BY K_Code
        ) AS T
            INNER JOIN Tbl_ClubKalaPolicy
                ON T.K_Code = dbo.Tbl_ClubKalaPolicy.K_code
        WHERE T.SumQty >= K_Qty;
    END;
    FETCH NEXT FROM CursorSelect
    INTO @Score,
         @IsDecimal,
         @DefinePrice,
         @FromDay,
         @ToDay,
         @ScorePrice,
         @Percent,
         @ScoreType,
         @StartDate,
         @EndDate,
         @Gold,
         @Silver,
         @boronz,
         @Normal;

----------------------------------------------------
END;
CLOSE CursorSelect;
DEALLOCATE CursorSelect;

-----------------------------------------------------------------------------------
BEGIN TRY
    BEGIN TRAN;
    IF (@TotPrice >= @DefinePrice)
       OR (ISNULL(@ClubId, 0) <> 0)
    BEGIN
        IF ISNULL(@ClubId, 0) <> 0
        BEGIN
            INSERT INTO dbo.Tbl_ScoreClub
            (
                Id_Club,
                NuFactor,
                Year,
                PriceFactor,
                Score,
                Id_Havaleh,
                FactorGuid
            )
            SELECT @ClubId,
                   @TmpId,
                   @Year,
                   @TotPrice,
                   ISNULL(@ChargeScore, 0),
                   @lastId,
                   @FactorId;
            SELECT @ScoreId = SCOPE_IDENTITY();
            IF ISNULL(@ChargePrice, 0) > 0
            BEGIN
                IF @ScoreType IN ( 2, 3 )
                    SET @FromDate = GETDATE();
                ELSE IF @ScoreType IN ( 4, 5 )
                    SET @FromDate = @StartDate;

                INSERT INTO dbo.ClubDetailScore
                (
                    ScoreId,
                    StartDate,
                    EndDate,
                    ChargePrice,
                    FactorGuid
                )
                VALUES
                (@ScoreId, DATEADD(DAY, @FromDay, @FromDate), DATEADD(DAY, @ToDay + @FromDay + 1, @FromDate),
                 @ChargePrice, @FactorId);
            END;
        END;

        IF ISNULL(@Mobile, '') = ''
            SELECT @Mobile = Mobile
            FROM dbo.Tbl_ClubH
            WHERE AutoId = @ClubId;

        SELECT @ServiceId = ServiceId
        FROM dbo.SMS_AutoSendingSettings
        WHERE ServiceId IN ( 1, 2, 3 )
              AND IsActive = 1;
        IF (ISNULL(@ServiceId, 0) <> 0 AND ISNULL(@Mobile, '') <> '')
        BEGIN
            INSERT INTO dbo.tblReceivedSms
            (
                Mobile,
                [Message]
            )
            VALUES
            (@Mobile, CAST(@ServiceId AS VARCHAR(10)));
        END;


    END;
    ---------------------(Charge Club )-----------------
    IF ISNULL(@Mobile, '') = ''
        SELECT @Mobile = Mobile
        FROM dbo.Tbl_ClubH
        WHERE AutoId = @ClubId;
    EXEC dbo.Club_Charge @Mobile, @TmpId, @lastId, @FactorId;


    COMMIT TRAN;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(MAX),
            @ErrorSeverity INT,
            @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE() + N' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
    SET @Msg = -5;
    ROLLBACK TRANSACTION;
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN @Msg;
END CATCH;


